﻿//2011-01-11    Initial                     By 應翰

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

//Using Namespace
using System.Data;
using System.Data.SqlClient;

namespace DataAccess
{
    public class DBAccess
    {
        string strSQL;
        DataTable dtTemp;
        string strUpdateTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");

        public DataTable getOrgGroupTable(string customerId)
        {
            string strSQL = "select * from c_org_group where _customer_id='" + customerId + "'";

            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }

        public DataTable getControllerTable(string customerId)
        {
            string strSQL = "select * from c_controller where _customer_id='" + customerId + "'";

            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }

        public DataTable getDeviceTable(string customerId)
        {
            string strSQL = "select * from c_device where _customer_id='" + customerId + "'";

            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }

        public DataTable getTagTable(string customerId)
        {
            string strSQL = "select * from c_tag where _customer_id='" + customerId + "'";

            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }

        public DataTable getUserData(string customerId)
        {
            string strSQL = "select * from c_user";

            DataTable dt = new CommonFun().GenDataTable(strSQL);

            if (dt != null)
                for (int i = dt.Rows.Count - 1; i >= 0; i--)
                {
                    if (dt.Rows[i]["_customer_id"].ToString() != customerId)
                        dt.Rows.Remove(dt.Rows[i]);
                }

            return dt;
        }

        public bool updateUserData(string enable, string name, string type,
                                        string account, string password, string phoneOffice,
                                        string phoneMobile, string email)
        {
            string strSQL = "UPDATE c_user SET _enable='" + enable + "'" +
                            ", _name='" + name + "'" +
                            ", _type='" + type + "'" +
                            ", _password='" + password + "'" +
                            ", _phone_office='" + phoneOffice + "'" +
                            ", _phone_mobile='" + phoneMobile + "'" +
                            ", _mail='" + email + "'" +
                            " WHERE _account='" + account + "'";

            return new CommonFun().ExeSQL(strSQL);
        }

        public bool deleteUserData(string account)
        {
            string strSQL = "DELETE From c_user WHERE _account='" + account + "'";

            return new CommonFun().ExeSQL(strSQL);
        }

        public bool createUserData(string enable, string name, string type,
                                        string account, string password, string phoneOffice,
                                        string phoneMobile, string email)
        {
            string strSQL = "INSERT INTO c_user (_customer_id, _enable, _name, _type, _account, _password, _phone_office, _phone_mobile, _mail) VALUES ('1'" +
                                                ", '" + enable + "'" +
                                                ", '" + name + "'" +
                                                ", '" + type + "'" +
                                                ", '" + account + "'" +
                                                ", '" + password + "'" +
                                                ", '" + phoneOffice + "'" +
                                                ", '" + phoneMobile + "'" +
                                                ", '" + email + "')";

            return new CommonFun().ExeSQL(strSQL);
        }


        // 20100221 appended
        /*************************************************************************************/
        public DataTable getModbusTagTable()
        {
            string strSQL = "select * from c_modbus_tag";

            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }

        public DataTable getModbusControlerTable()
        {
            string strSQL = "select * from c_modbus_controller";

            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }
        /*************************************************************************************/

        // YHLiu appended
        /*************************************************************************************/
        public DataTable getCtrlServerTable(string customerId)
        {
            string strSQL = "select * from c_ctrl_server where _customer_id='" + customerId + "'";

            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }
        
        public DataTable getModbusControllerTable(string controllerID)
        {
            string strSQL = "select * from c_modbus_controller where _id='" + controllerID + "'";

            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }



        public bool addControllerData(string name, string connection_type, string slave_unit_id,
                                string ip, string port, string desc, string ref_id, string ctrl_server)
        {
            string strSQL = "INSERT INTO c_controller (_datetime,_enabled,_customer_id,_ctrl_server_id,_ref_id,_model,_status,_name,_desc,_protocol,_firmware)";
            strSQL = strSQL + " Values( getdate(), '1', '1', '" + ctrl_server + "', '" + ref_id + "', 'MODBUS', 0, '" + name + "','" + desc + "', 'MODBUS','1.0.0')";

            bool blnRet = new CommonFun().ExeSQL2(strSQL);
            if (blnRet == true)
            {
                strSQL = "select * from c_controller where _ref_id='" + ref_id + "'";

                DataTable dt = new CommonFun().GenDataTable2(strSQL);

                string id = dt.Rows[0]["_id"].ToString();

                strSQL = "INSERT INTO c_modbus_controller (_id,_manufacturer,_connection_type,_master_unit_id,_slave_unit_id,_port_name,_baud_rate,_stop_bits,_parity_check,_databits,_ip,_port)";
                strSQL = strSQL + "Values ('" + id + "', 'MODBUS', '" + connection_type + "', 0, '" + slave_unit_id + "', 'COM1', 9600, 1,'None',8, '" + ip + "','" + port + "')";

                bool blnRet2 = new CommonFun().ExeSQL2(strSQL);

                if (blnRet2 == false)
                {
                    strSQL = "delete from c_controller where _id='" + id + "'";

                    new CommonFun().ExeSQL2(strSQL);
                }
                return blnRet2;
            }
            else
                return blnRet;

        }


        public bool deleteControllerData(string controllerID)
        {
            string strSQL = "begin delete from c_modbus_controller where _id='" + controllerID + "'";
            strSQL = strSQL + " delete from c_controller where _id='" + controllerID + "' end;";

            return new CommonFun().ExeSQL2(strSQL);
        }

        public bool updateControllerData(string name, string connection_type, string slave_unit_id,
                        string ip, string port, string desc, string id, string ctrl_server)
        {
            string strSQL = "begin update c_controller Set _name='" + name + "', _desc='" + desc + "', _ctrl_server_id='" + ctrl_server + "' where _id='" + id + "'";
            strSQL = strSQL + " update c_modbus_controller set _connection_type='" + connection_type + "', _slave_unit_id='" + slave_unit_id + "', ";
            strSQL = strSQL + " _ip='" + ip + "', _port='" + port + "' where _id='" + id + "' end;";
            return new CommonFun().ExeSQL2(strSQL);

        }

        public bool addDeviceData(string name, string desc, string ctrl_server_id, string controller_id,
                        string org_group_id, string ref_id)
        {
            string strSQL = "INSERT INTO c_device (_datetime,_enabled, _hidden,_customer_id,_ctrl_server_id,_controller_id,_org_group_id,_ref_id,_type,_subtype,_status,_name,_desc)";
            strSQL = strSQL + " Values( getdate(), 1, 0, 1, '" + ctrl_server_id + "', '" + controller_id + "', '" + org_group_id + "', '" + ref_id + "', 4, -1, 0, '" + name + "', '" + desc + "')";

            return new CommonFun().ExeSQL2(strSQL);


        }

        public bool deleteDeviceData(string id)
        {
            string strSQL = "delete from c_device where _id='" + id + "'";

            return new CommonFun().ExeSQL2(strSQL);
        }

        public bool updateDeviceData(string name, string desc, string org_group_id, string id)
        {
            string strSQL = "update c_device Set _name='" + name + "', _desc='" + desc + "', _org_group_id='" + org_group_id + "' where _id='" + id + "'";
            return new CommonFun().ExeSQL2(strSQL);

        }

        public bool addTagData(string ctrl_server_id, string controller_id, string device_id, int type,
                        string attribute, string name, string label, string desc,
                        string unit, double weight, double bias, string model_type,
                        string data_type, string data_length, int bit_address,
                        string register_address, int endianness)
        {
            string strSQL = "insert c_tag (_datetime, _enabled, _hidden, _customer_id, _ctrl_server_id, _controller_id, _device_id, _type, ";
            strSQL = strSQL +"_attribute,_model, _status, _name, _label,_desc,_unit,_weight,_bias,_formula,_special_action)";
            strSQL = strSQL + " Values( getdate(), '1', '0', '1', '" + ctrl_server_id + "', '" + controller_id + "', '" + device_id + "', '" + type + "',";
            strSQL = strSQL + "'" + attribute + "', 1, 1, '" + name + "','" + label + "','" + desc + "','"+ unit +"',";
            strSQL = strSQL + "" + weight + ", "+ bias +", 0,0)";

            bool blnRet = new CommonFun().ExeSQL2(strSQL);
            if (blnRet == true)
            {
                strSQL = "select * from c_tag where _controller_id='" + controller_id + "' and _device_id='" + device_id + "' and _name='" + name + "'";

                DataTable dt = new CommonFun().GenDataTable2(strSQL);

                string id = dt.Rows[0]["_id"].ToString();

                strSQL = "INSERT INTO c_modbus_tag (_id, _model_type, _data_type, _data_length , _bit_address, _register_address, _endianness)";
                strSQL = strSQL + "Values ('" + id + "', '" + model_type + "','" + data_type + "','" + data_length + "', " + bit_address + ",'" + register_address + "'," + endianness + ")";

                bool blnRet2 = new CommonFun().ExeSQL2(strSQL);

                if (blnRet2 == false)
                {
                    strSQL = "delete from c_tag where _id='" + id + "'";

                    new CommonFun().ExeSQL2(strSQL);
                }
                return blnRet2;
            }
            else
                return blnRet;

        }

        public bool deleteTagData(string tagID)
        {
            string strSQL = "begin delete from c_modbus_tag where _id='" + tagID + "'";
            strSQL = strSQL + " delete from c_tag where _id='" + tagID + "' end;";

            return new CommonFun().ExeSQL2(strSQL);
        }

        public DataTable getModbusTagTable(string Tagid)
        {
            string strSQL = "select * from c_modbus_tag where _id='" + Tagid + "'";

            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }


        public bool updateTagData(string TagID, int type,
                       string attribute, string name, string label, string desc,
                       string unit, double weight, double bias, string model_type,
                       string data_type, string data_length, int bit_address,
                       string register_address, int endianness)
        {
            string strSQL = "begin update c_tag set _type=" + type + ", _attribute='" + attribute + "', _name='" + name + "',";
            strSQL = strSQL + "_label='" + label + "', _desc='" + desc + "', _unit='" + unit + "', _weight=" + weight + ",";
            strSQL = strSQL + "_bias=" + bias + "  where _id='" + TagID + "';";
            strSQL = strSQL + " update c_modbus_tag set _model_type='" + model_type + "', _data_type='" + data_type + "',";
            strSQL = strSQL + "_data_length='" + data_length + "' , _bit_address=" + bit_address + ",";
            strSQL = strSQL + "_register_address='" + register_address + "', _endianness=" + endianness + " where _id='" + TagID + "'; end";

            return new CommonFun().ExeSQL2(strSQL);

        }


        public bool addOrgGroup(string parent_id, string name, string desc, string ref_id)
        {
            string strSQL = "Select * from c_org_group where _id='" + parent_id + "'";
            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            int intlevel=0;
            if (parent_id == "-1")
                intlevel = 0;
            else
                intlevel = int.Parse(dt.Rows[0]["_level"].ToString());

            intlevel = intlevel + 1;
            
            string strType = "";
            switch (intlevel)
            {
                case 1:
                    strType = "BUILDING";
                    break;
                case 2:
                    strType = "FLOOR";
                    break;
                case 3:
                    strType = "BLOCK";
                    break;
            }
            int intLeaf = 0;
            if (intlevel == 3)
                intLeaf = 1;

            if (parent_id == "-1")
                parent_id = "0";

            strSQL = "INSERT INTO c_org_group (_datetime,_enabled, _customer_id,_parent_id,_level, _leaf,_ref_id,_category,_type,_name,_desc)";
            strSQL = strSQL + " Values( getdate(), 1, 1, " + parent_id + ", " + intlevel + ", '" + intLeaf + "', '" + ref_id + "', 'LOCATION', '" + strType + "', '" + name + "', '" + desc + "')";

            return new CommonFun().ExeSQL2(strSQL);
        }

        public bool updateOrgGroup(string id, string name, string desc)
        {
            string strSQL = "update c_org_group Set _name='" + name + "', _desc='" + desc + "' where _id='" + id + "'";
            return new CommonFun().ExeSQL2(strSQL);

        }

        public bool deleteOrgGroup(string id)
        {
            string strSQL = "Select * from c_org_group where _parent_id='" + id + "'";
            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            if (dt.Rows.Count > 0)
            {
                return false;
            }
            else
            {
                strSQL = "delete from c_org_group where _id='" + id + "'";
                return new CommonFun().ExeSQL2(strSQL);
            }
            
        }

        public DataTable getBillModeTable()
        {
            string strSQL = "select _id ,_year , CONVERT(varchar(12) , _begin_time, 111 ) as _begin_time, CONVERT(varchar(12) , _end_time, 111 ) as _end_time,_mode"
                + ",_updatetime from c_billing_mode order by  _mode desc,_year desc";

            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }

        public DataTable getBillModeBillTable(string strID)
        {
            string strSQL = "select * from c_billing_mode_bill where _id='" + strID + "' order by _kwh_start";

            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }

        public bool addBillModeData(string year, DateTime begin_time, DateTime end_time, string mode, double bill)
        {
            string strSQL = "INSERT INTO c_billing_mode (_year ,_begin_time ,_end_time ,_mode)";
            strSQL = strSQL + " Values('" + year + "','" + begin_time.ToString("s") + "','" + end_time.ToString("s") + "','" + mode + "')";

            bool blnRet = new CommonFun().ExeSQL2(strSQL);
            if (blnRet == true)
            {

                if (mode == "3" || mode == "4")
                {
                    strSQL = "select * from c_billing_mode where _year='" + year + "' and _mode='" + mode + "'";

                    DataTable dt = new CommonFun().GenDataTable2(strSQL);

                    string id = dt.Rows[0]["_id"].ToString();

                    strSQL = "INSERT INTO c_billing_mode_bill (_id ,_kwh_start ,_kwh_end ,_bill)";
                    strSQL = strSQL + "Values ('" + id + "', '', '9999999', " + bill + ")";

                    bool blnRet2 = new CommonFun().ExeSQL2(strSQL);

                    if (blnRet2 == false)
                    {
                        strSQL = "delete from c_billing_mode where _id='" + id + "'";

                        new CommonFun().ExeSQL2(strSQL);
                    }
                    return blnRet2;
                }
                else
                    return blnRet;
            }
            else
                return blnRet;

        }

        public bool updateBillModeData(string id, DateTime begin_time, DateTime end_time, double bill)
        {
            string strSQL = "begin update c_billing_mode Set _begin_time='" + begin_time.ToString("s") + "',"+
                "_end_time='" + end_time.ToString("s") + "' where _id='" + id + "';"+
                " update c_billing_mode_bill set _bill=" + bill + " where _id='" + id + "'; end";
            return new CommonFun().ExeSQL2(strSQL);

        }

        public bool deleteBillModeData(string strID)
        {
            string strSQL = "begin delete from c_billing_mode_bill where _id='" + strID + "'";
            strSQL = strSQL + " delete from c_billing_mode where _id='" + strID + "' end;";

            return new CommonFun().ExeSQL2(strSQL);
        }

        public bool addBillModeBillData(string id, int kwh_start, int kwh_end, double bill)
        {
            string strSQL = "INSERT INTO c_billing_mode_bill (_id ,_kwh_start ,_kwh_end ,_bill)";
            strSQL = strSQL + " Values('" + id + "',"+ kwh_start +"," + kwh_end+",'" + bill + "')";

            return new CommonFun().ExeSQL2(strSQL);

        }

        public bool deleteBillModeBillData(string strID, int kwh_start, int kwh_end)
        {
            string strSQL = "delete from c_billing_mode_bill where _id='" + strID + "' and _kwh_start='" + kwh_start + "' and _kwh_end='" + kwh_end + "'";

            return new CommonFun().ExeSQL2(strSQL);
        }

        public bool addEventLog(string account, string ip, int event_id, string _event_memo, string success)
        {
            
            string strSQL = "INSERT INTO c_event_log (_account ,_ip ,_event_id, _event_memo, _success)";
            strSQL = strSQL + " Values('" + account + "','" + ip + "'," + event_id + ",'" + _event_memo + "', '" + success + "')";
            
            return new CommonFun().ExeSQL2(strSQL);

        }

        public bool addEventLog(string account, string ip, int event_id, string _event_memo, string success, int org_id)
        {

            string strSQL = "INSERT INTO c_event_log (_account ,_ip ,_event_id, _event_memo, _success, _org_id)";
            strSQL = strSQL + " Values('" + account + "','" + ip + "'," + event_id + ",'" + _event_memo + "', '" + success + "', " + org_id + ")";

            return new CommonFun().ExeSQL2(strSQL);

        }

        public DataTable getEventTable()
        {
            string strSQL = "SELECT * FROM c_event";

            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }

        public DataTable getEventLogTable()
        {
            string strSQL = "SELECT l._id, l._account, l._ip, e._category, e._event_type, l._event_memo, l._updatetime, l._success, l._org_id"
                + " FROM c_event_log l, c_event e where l._event_id=e._id";

            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }

        public DataTable getEventLogTable(string category, string event_type, string start_time, string end_time)
        {
            string strSQL = "SELECT l._id, l._account, l._ip, e._category, e._event_type, l._event_memo, l._updatetime, l._success, l._org_id"
                + " FROM c_event_log l, c_event e where l._event_id=e._id ";

            if (category !="")
            {
                strSQL = strSQL + "and _category='" + category + "' ";
            }

            if (event_type != "")
            {
                strSQL = strSQL + "and _event_type='" + event_type + "' ";
            }

            if (start_time != "")
            {
                strSQL = strSQL + "and _updatetime>='" + DateTime.Parse(start_time).ToString("s") + "' ";
            }
            
            if (end_time != "")
            {
                strSQL = strSQL + "and _updatetime<='" + DateTime.Parse(end_time).AddDays(1).ToString("s") + "' ";
            }


            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }

        public DataTable getAlarmMessageTable(string start_time, string end_time)
        {
            string strSQL = "SELECT * FROM c_alarm_message where _name='寢室用電量監控告警' ";

            if (start_time != "")
            {
                strSQL = strSQL + "and _datetime>='" + DateTime.Parse(start_time).ToString("s") + "' ";
            }

            if (end_time != "")
            {
                strSQL = strSQL + "and _datetime<='" + DateTime.Parse(end_time).AddDays(1).ToString("s") + "' ";
            }


            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }

        public double getMeterBalance(string deviceID)
        {


            return 30.5;
        }

        public bool setMeterBalance(string deviceID, double balance)
        {


            return true;
        }

        #region gtkuo add for Alarmy (gtkuo add for Alarm)
        /*************************************************************************************/

        // gtkuo add for Alarm
        /*************************************************************************************/
        public DataTable getAlarmGrpTable()
        {
            string strSQL = "SELECT * FROM c_alarm_group";

            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }

        public DataTable getAlarmGrpTable(int _id)
        {
            string strSQL = "SELECT * FROM c_alarm_group WHERE _id = '" + _id + "'";

            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }

        public bool addAlarmGrp(string GrpName)
        {
            bool addSuc = false;
            string strSQL = "INSERT INTO c_alarm_group(_customer_id, _name, _enabled) VALUES('1', '" + GrpName + "','1')";

            addSuc = new CommonFun().ExeSQL2(strSQL);

            return addSuc;
        }

        public bool updateAlarmGrp(int _id, string _name, int _enabled)
        {
            bool updateSuc = false;
            string strSQL = "UPDATE c_alarm_group SET _customer_id = '1', _name = '" + _name + "', _enabled = '" + _enabled + "' WHERE _id = '" + _id + "'";

            updateSuc = new CommonFun().ExeSQL2(strSQL);

            return updateSuc;
        }

        public bool deleteAlarmGrp(string _id)
        {
            bool deleteSuc = false;
            string strSQL = "DELETE c_alarm_group_user WHERE _group_id = '" + _id + "';"
                + "DELETE c_alarm_dormitory_group WHERE _group_id = '" + _id + "';"
                + "DELETE c_alarm_group WHERE _id = '" + _id + "'";

            deleteSuc = new CommonFun().ExeSQL2(strSQL);

            return deleteSuc;
        }

        public DataTable getAlarmMemberTable()
        {
            string strSQL = "select * from c_alarm_user";

            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }

        public bool addAlarmMember(string MemberName, string MemberPhone, string MemberEmail, string MemberDesc)
        {
            bool addSuc = false;
            string strSQL = "INSERT INTO c_alarm_user(_ref_id, _customer_id, _name, _desc, _email, _phone) VALUES('1', '1', '" + MemberName + "','" + MemberDesc + "','" + MemberEmail + "', '" + MemberPhone + "')";

            addSuc = new CommonFun().ExeSQL2(strSQL);

            return addSuc;
        }

        public bool updateAlarmMember(int _id, string _name, string _phone, string _email, string _desc)
        {
            bool updateSuc = false;
            string strSQL = "UPDATE c_alarm_user SET _ref_id = '1', _customer_id = '1', _name = '" + _name + "', _desc = '" + _desc + "', _email = '" + _email + "', _phone = '" + _phone + "' WHERE _id = '" + _id + "'";

            updateSuc = new CommonFun().ExeSQL2(strSQL);

            return updateSuc;
        }

        public bool deleteAlarmMember(string _id)
        {
            bool deleteSuc = false;
            string strSQL = "DELETE c_alarm_group_user WHERE _user_id = '" + _id + "';"
                + "DELETE c_alarm_user WHERE _id = '" + _id + "'";

            deleteSuc = new CommonFun().ExeSQL2(strSQL);

            return deleteSuc;
        }

        public DataTable getAlarmGrpMemberTable()
        {
            string strSQL = "SELECT _group_id, _user_id, b._name _groupname, c._name _username, _phone, _email, _desc  FROM c_alarm_group_user AS a, c_alarm_group AS b, c_alarm_user AS c WHERE a._group_id = b._id AND a._user_id = c._id";

            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }

        public DataTable getAlarmGrpMemberTable(string _group_id)
        {
            string strSQL = "SELECT _group_id, _user_id, b._name _groupname, c._name _username, _phone, _email, _desc  FROM c_alarm_group_user AS a, c_alarm_group AS b, c_alarm_user AS c WHERE _group_id = '" + _group_id + "' AND a._group_id = b._id AND a._user_id = c._id";

            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }

        public bool addAlarmGrpMember(string GrpID, string MemberID)
        {
            bool addSuc = false;
            string strSQL = "INSERT INTO c_alarm_group_user(_group_id, _user_id ) VALUES('" + GrpID + "', '" + MemberID + "')";

            addSuc = new CommonFun().ExeSQL2(strSQL);

            return addSuc;
        }

        public bool deleteAlarmGrpMember(string _group_id, string _user_id)
        {
            bool deleteSuc = false;
            string strSQL = "DELETE c_alarm_group_user WHERE _group_id = '" + _group_id + "' AND _user_id = '" + _user_id + "'";

            deleteSuc = new CommonFun().ExeSQL2(strSQL);

            return deleteSuc;
        }

        public DataTable getAlarmBuildingTable()
        {
            bool insertSuc = false;
            string strSQL = "SELECT a._id, a._name, a._desc, b._value FROM c_org_group AS a LEFT OUTER JOIN c_alarm_building AS b ON a._id = b._org_group_id WHERE _level = '1' ";

            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            for (int i = 0; i < dt.Rows.Count; i++)
                if (dt.Rows[i]["_value"].ToString() == "")
                    insertSuc = new CommonFun().ExeSQL2("INSERT INTO c_alarm_building(_org_group_id, _value ) VALUES('" + dt.Rows[i]["_id"].ToString() + "', '')");

            if (insertSuc)
                dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }

        public bool updateAlarmBuilding(int _id, string _value)
        {
            bool updateSuc = false;
            string strSQL =
                "UPDATE c_alarm_building SET _value = '" + _value + "' WHERE _org_group_id = '" + _id + "'";

            updateSuc = new CommonFun().ExeSQL2(strSQL);

            return updateSuc;
        }

        public DataTable getAlarmDormitoryTable(int _parent_id)
        {
            bool insertSuc = false;
            DataTable dt = new DataTable();

            if (_parent_id == 0)
            {
                string strSQL = "SELECT a._id, a._parent_id, a._name, a._desc, b._id _dormitory_id ,b._value, b._enabled FROM c_org_group AS a LEFT OUTER JOIN c_alarm_dormitory AS b ON a._id = b._org_group_id WHERE _level = '3'";
                dt = new CommonFun().GenDataTable2(strSQL);

                for (int i = 0; i < dt.Rows.Count; i++)
                    if (dt.Rows[i]["_value"].ToString() == "")
                        insertSuc = new CommonFun().ExeSQL2("INSERT INTO c_alarm_dormitory(_org_group_id, _value, _enabled ) VALUES('" + dt.Rows[i]["_id"].ToString() + "', '', '1')");

                dt = new DataTable();
            }
            else
            {
                string strSQL = "SELECT a._id, a._parent_id, a._name, a._desc, b._id _dormitory_id ,b._value, b._enabled FROM c_org_group AS a LEFT OUTER JOIN c_alarm_dormitory AS b ON a._id = b._org_group_id WHERE _level = '3' AND _parent_id IN (SELECT _id FROM c_org_group WHERE _parent_id = '" + _parent_id + "')";
                dt = new CommonFun().GenDataTable2(strSQL);
            }

            return dt;
        }

        public DataTable getAlarmDormitoryTable()
        {
            DataTable dt = new DataTable();

            string strSQL = "SELECT a._id, a._parent_id, a._name, a._desc, b._id _dormitory_id ,b._value, b._enabled FROM c_org_group AS a LEFT OUTER JOIN c_alarm_dormitory AS b ON a._id = b._org_group_id WHERE _level = '3'";
            dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }

        public bool updateAlarmDormitory(int _id, string _value, int _enabled)
        {
            bool updateSuc = false;
            string strSQL =
                "UPDATE c_alarm_dormitory SET _value = '" + _value + "', _enabled = '" + _enabled + "' WHERE _org_group_id = '" + _id + "'";

            updateSuc = new CommonFun().ExeSQL2(strSQL);

            return updateSuc;
        }

        public DataTable getOrgGroupTable()
        {
            string strSQL = "select * from c_org_group";

            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }

        public DataTable getAlarmDormitoryGrpTable()
        {
            string strSQL = "SELECT _dormitory_id, _group_id, orgID, dormitoryEnabled, dormitoryName, dormitoryDesc, _enabled groupEnabled , _name groupName FROM c_alarm_dormitory_group AS a, (SELECT dormitory._id dormitoryID, dormitory._org_group_id orgID, dormitory._enabled dormitoryEnabled, org._name dormitoryName, org._desc dormitoryDesc FROM c_alarm_dormitory AS dormitory LEFT OUTER JOIN c_org_group AS org ON dormitory._org_group_id = org._id) AS b, c_alarm_group AS c WHERE a._dormitory_id = b.dormitoryID AND a._group_id = c._id";

            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }

        public DataTable getAlarmDormitoryGrpTable(string _dormitory_id)
        {
            string strSQL = "SELECT _dormitory_id, _group_id, orgID, dormitoryEnabled, dormitoryName, dormitoryDesc, _enabled groupEnabled , _name groupName FROM c_alarm_dormitory_group AS a, (SELECT dormitory._id dormitoryID, dormitory._org_group_id orgID, dormitory._enabled dormitoryEnabled, org._name dormitoryName, org._desc dormitoryDesc FROM c_alarm_dormitory AS dormitory LEFT OUTER JOIN c_org_group AS org ON dormitory._org_group_id = org._id) AS b, c_alarm_group AS c WHERE a._dormitory_id = '" + _dormitory_id + "' AND a._dormitory_id = b.dormitoryID AND a._group_id = c._id";

            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            return dt;
        }

        public bool addAlarmDormitoryGrp(string DormitoryID, string GrpID)
        {
            bool addSuc = false;

            string strSQL = "INSERT INTO c_alarm_dormitory_group(_dormitory_id, _group_id) VALUES('" + DormitoryID + "', '" + GrpID + "')";

            addSuc = new CommonFun().ExeSQL2(strSQL);

            return addSuc;
        }

        public bool deleteAlarmDormitoryGrp(string _dormitory_id, string _group_id)
        {
            bool deleteSuc = false;
            //string strSQL = "DELETE c_alarm_group_user WHERE _group_id = '" + _group_id + "' AND _user_id = '" + _user_id + "'";
            string strSQL = "DELETE c_alarm_dormitory_group WHERE _dormitory_id = '" + _dormitory_id + "' AND _group_id = '" + _group_id + "'";

            deleteSuc = new CommonFun().ExeSQL2(strSQL);

            return deleteSuc;
        }
        /*************************************************************************************/
        #endregion




        #region 報表用的DB Query (added by sychiu)
        /// <summary>
        /// 取得某層級中所有的建築物
        /// </summary>
        /// <param name="iLevel"></param>
        /// <returns></returns>
        public DataTable getOrgGroupTableByLevel(int iLevel)
        {
            string strSQL = "select * from c_org_group where _level='" + iLevel + "'";
            DataTable dt = new CommonFun().GenDataTable2(strSQL);
            return dt;
        }

        /// <summary>
        /// 查每間寢室的電錶
        /// </summary>
        /// <param name="iRoom"></param>
        /// <returns></returns>
        public int getElectricMeterByRoom(int iRoom)
        {
            string strSQL = "select c_tag._id as id from c_device inner join c_tag on c_device._id=c_tag._device_id where c_device._org_group_id=" + iRoom + " and c_tag._name='用電度數'";
            DataTable dt = new CommonFun().GenDataTable2(strSQL);

            int iElectricMeter = -1;
            if (dt.Rows.Count > 0)
            {
                iElectricMeter = Convert.ToInt32(dt.Rows[0]["id"].ToString());
            }
            return iElectricMeter;
        }

        /// <summary>
        /// 由Tag_etldataTable取得某日的所有資料
        /// </summary>
        /// <param name="iTagID"></param>
        /// <param name="dtDate"></param>
        /// <returns></returns>
        public DataTable getTag_etldataTableByTagID(int iTagID, DateTime dtDate)
        {
            //select * from l_tag_etldata_201104 where _tag_id=4 and _end_time >= '2011/4/30 00:00:00' and _end_time<= '2011/5/1 00:00:00' order by _start_time
            string sTableName = "l_tag_etldata_" + dtDate.ToString("yyyyMM");
            string sBgnTime = dtDate.ToString("yyyy/MM/dd 00:00:00");
            string sEndTime = dtDate.AddDays(1).ToString("yyyy/MM/dd 00:00:00");

            string strSQL = "select * from " + sTableName + " where _tag_id=" + iTagID + " and _end_time >= '" + sBgnTime + "' and _end_time<= '" + sEndTime + "' order by _start_time";
            DataTable dt = new CommonFun().GenDataTable3(strSQL);

            if (dtDate.Day == 1)    //當月第一天，要再去取前一個月的table
            {
                sTableName = "l_tag_etldata_" + dtDate.AddDays(-1).ToString("yyyyMM");
                strSQL = "select * from " + sTableName + " where _tag_id=" + iTagID + " and _end_time >= '" + sBgnTime + "' and _end_time<= '" + sBgnTime + "' order by _start_time";
                DataTable dtPre = new DataTable();
                dtPre = new CommonFun().GenDataTable3(strSQL);
                if (dt != null && dt.Rows.Count > 0)
                    dtPre.Merge(dt);
                return dtPre;
            }

            return dt;
        }

        /// <summary>
        /// 由Tag_etldataTable取得某日的第一筆資料
        /// </summary>
        /// <param name="iTagID"></param>
        /// <param name="dtDate"></param>
        /// <returns></returns>
        public DataTable getTag_etldataTableFirstRecordOfDayByTagID(int iTagID, DateTime dtDate)
        {
            string sTableName = "";
            string sBgnTime = dtDate.ToString("yyyy/MM/dd 00:00:00");

            DataTable dt = new DataTable();
            string strSQL = "";
            if (dtDate.Day == 1)    //當月第一天，要去取前一個月的table
            {
                sTableName = "l_tag_etldata_" + dtDate.AddDays(-1).ToString("yyyyMM");
                strSQL = "select top 1 * from " + sTableName + " where _tag_id=" + iTagID + " and _end_time >= '" + sBgnTime + "' order by _start_time";
                dt = new CommonFun().GenDataTable3(strSQL);
                if (dt != null && dt.Rows.Count > 0)
                    return dt;
            }

            sTableName = "l_tag_etldata_" + dtDate.ToString("yyyyMM");
            strSQL = "select top 1 * from " + sTableName + " where _tag_id=" + iTagID + " and _end_time >= '" + sBgnTime + "' order by _start_time";
            dt = new CommonFun().GenDataTable3(strSQL);
            return dt;
        }

        /// <summary>
        /// 由Tag_etldataTable取得某日的最後一筆資料
        /// </summary>
        /// <param name="iTagID"></param>
        /// <param name="dtDate"></param>
        /// <returns></returns>
        public DataTable getTag_etldataTableLastRecordOfDayByTagID(int iTagID, DateTime dtDate)
        {
            string sTableName = "l_tag_etldata_" + dtDate.ToString("yyyyMM");
            string sEndTime = dtDate.AddDays(1).ToString("yyyy/MM/dd 00:00:00");

            string strSQL = "";
            //取最後一筆
            strSQL = "select top 1 * from " + sTableName + " where _tag_id=" + iTagID + " and _end_time<= '" + sEndTime + "' order by _start_time desc";

            DataTable dt = new CommonFun().GenDataTable3(strSQL);
            return dt;
        }

        /// <summary>
        /// 由Tag_etldataTable取得當年某日之後的第一筆資料
        /// </summary>
        /// <param name="iTagID"></param>
        /// <param name="dtDate"></param>
        /// <returns></returns>
        public DataTable getTag_etldataTableFirstRecordOfYearByTagID(int iTagID, DateTime dtDate)
        {
            string sTableName = "";
            string sBgnTime = dtDate.ToString("yyyy/MM/dd 00:00:00");
            DateTime dtEndDate = new DateTime(dtDate.Year + 1, 1, 1);

            DataTable dt = new DataTable();
            string strSQL = "";
            if (dtDate.Day == 1)    //當月第一天，要去取前一個月的table
            {
                while (dtDate <= dtEndDate)
                {
                    sTableName = "l_tag_etldata_" + dtDate.AddDays(-1).ToString("yyyyMM");
                    int iCount = CheckTableisExist(sTableName); //檢查table是否存在
                    if (iCount > 0)
                    {
                        strSQL = "select top 1 * from " + sTableName + " where _tag_id=" + iTagID + " and _end_time >= '" + sBgnTime + "' order by _start_time";
                        dt = new CommonFun().GenDataTable3(strSQL);
                    }
                    if (dt != null && dt.Rows.Count > 0)
                        return dt;
                    dtDate = dtDate.AddMonths(1);
                }
                return dt;
            }
            else
            {
                while (dtDate <= dtEndDate)
                {
                    sTableName = "l_tag_etldata_" + dtDate.ToString("yyyyMM");
                    int iCount = CheckTableisExist(sTableName); //檢查table是否存在
                    if (iCount > 0)
                    {
                        strSQL = "select top 1 * from " + sTableName + " where _tag_id=" + iTagID + " and _end_time >= '" + sBgnTime + "' order by _start_time";
                        dt = new CommonFun().GenDataTable3(strSQL);
                    }
                    if (dt != null && dt.Rows.Count > 0)
                        return dt;
                    dtDate = dtDate.AddMonths(1);
                }
                return dt;
            }
        }

        /// <summary>
        /// 由Tag_etldataTable取得當年某日之前的第一筆資料
        /// </summary>
        /// <param name="iTagID"></param>
        /// <param name="dtDate"></param>
        /// <returns></returns>
        public DataTable getTag_etldataTableLastRecordOfYearByTagID(int iTagID, DateTime dtDate)
        {
            string sTableName = "";
            string sEndTime = dtDate.AddDays(1).ToString("yyyy/MM/dd 00:00:00");
            DateTime dtEndDate = new DateTime(dtDate.Year, 1, 1);

            DataTable dt = new DataTable();
            string strSQL = "";
            while (dtDate >= dtEndDate)
            {
                sTableName = "l_tag_etldata_" + dtDate.ToString("yyyyMM");
                int iCount = CheckTableisExist(sTableName); //檢查table是否存在
                if (iCount > 0)
                {
                    strSQL = "select top 1 * from " + sTableName + " where _tag_id=" + iTagID + " and _end_time <= '" + sEndTime + "' order by _start_time desc";
                    dt = new CommonFun().GenDataTable3(strSQL);
                }
                if (dt != null && dt.Rows.Count > 0)
                    return dt;
                dtDate = dtDate.AddMonths(-1);
            }
            return dt;
        }

        /// <summary>
        /// 確認table是否存在
        /// </summary>
        /// <param name="sTableName"></param>
        /// <returns></returns>
        public int CheckTableisExist(string sTableName)
        {
            string strSQL = "SELECT COUNT(*) as count FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" + sTableName + "'";
            DataTable dt = new CommonFun().GenDataTable3(strSQL);
            return Convert.ToInt16(dt.Rows[0]["count"].ToString());
        }

        /// <summary>
        /// 查某學年度的電費計算週期
        /// </summary>
        /// <param name="iYear"></param>
        /// <returns></returns>
        public DataTable getBillingModeTableByYear(int iYear)
        {
            string strSQL = "select * from c_billing_mode where _year=" + iYear + "";
            DataTable dt = new CommonFun().GenDataTable2(strSQL);
            return dt;
        }

        /// <summary>
        /// 取得學年度資料
        /// </summary>
        /// <returns></returns>
        public DataTable getYearOfBillingModeTable()
        {
            string strSQL = "select distinct _year from c_billing_mode order by _year";
            DataTable dt = new CommonFun().GenDataTable2(strSQL);
            return dt;
        }

        /// <summary>
        /// 取得各週期的每度電費資料
        /// </summary>
        /// <returns></returns>
        public DataTable getBillingModeBillTableByID(int iID)
        {
            string strSQL = "select * from c_billing_mode_bill where _id=" + iID + " order by _kwh_start";
            DataTable dt = new CommonFun().GenDataTable2(strSQL);
            return dt;
        }
        #endregion

    }
}

